﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using DAL.Abstract;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.VisualBasic.FileIO;

namespace DAL.Class
{
    class PhantomsFileLoad : IFileLoad
    {
        private SqlDatabase dbInstance;

        public int BatchSize
        {
            get
            {
                return int.Parse(ConfigurationManager
                    .AppSettings["BatchSize"]);
            }
        }

        public string connectionString
        {
            get
            {
                return ConfigurationManager
                    .ConnectionStrings["InventizerConnectionString"]
                    .ConnectionString;
            }
        }

        public PhantomsFileLoad()
        {
            dbInstance = new SqlDatabase(connectionString);
        }

        protected const string _truncateLiveTableCommandText = @"TRUNCATE TABLE LABELS";

        public int LoadCsvDataIntoSqlServer(string filePath, string separator)
        {
            var createdCount = 0;
            try
            {
                using (var textFieldParser = new TextFieldParser(filePath))
                {
                    textFieldParser.TextFieldType = FieldType.Delimited;
                    textFieldParser.Delimiters = separator == "tab" ? new[] { "\t" } : new[] { separator };
                    textFieldParser.HasFieldsEnclosedInQuotes = true;
                    var dataTable = new DataTable("PHANTOMS");
                    dataTable.Columns.Add("PHANTOM");
                    dataTable.Columns.Add("PARTNUMBER");
                    dataTable.Columns.Add("CALCVALUE");
                    using (var sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();
                        using (var tran = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted))
                        {
                            try
                            {
                                using (
                                    var sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, tran))
                                {
                                    sqlBulkCopy.DestinationTableName = "PHANTOMS";
                                    sqlBulkCopy.ColumnMappings.Add("INVENTORYID", "INVENTORYID");
                                    sqlBulkCopy.ColumnMappings.Add("PHANTOM", "PHANTOM");
                                    sqlBulkCopy.ColumnMappings.Add("PARTNUMBER", "PARTNUMBER");
                                    sqlBulkCopy.ColumnMappings.Add("CALCVALUE", "CALCVALUE");
                                    var dc = new DataColumn
                                    {
                                        DefaultValue = 1,
                                        ColumnName = "INVENTORYID",
                                        DataType = typeof(int)
                                    };
                                    dataTable.Columns.Add(dc);
                                    while (!textFieldParser.EndOfData)
                                    {
                                        dataTable.Rows.Add(textFieldParser.ReadFields());
                                        createdCount++;
                                        if (createdCount % BatchSize == 0)
                                        {
                                            InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
                                        }
                                    }
                                    InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
                                }
                                tran.Commit();
                                sqlConnection.Close();
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();
                                throw new Exception("Blad w linii: " + createdCount + " " + ex.Message);
                            }
                        }
                    }
                }
                return createdCount;
            }
            catch (Exception ex)
            {
                throw new Exception("Blad w linii: " + createdCount + " " + ex.Message);
            }
        }

        private void InsertDataTable(SqlBulkCopy sqlBulkCopy, SqlConnection sqlConnection, DataTable dataTable)
        {
            sqlBulkCopy.WriteToServer(dataTable);
            dataTable.Rows.Clear();
        }
    }
}
